const router = require('koa-router')()

router.prefix('/information')
// 获取学院信息
router.get("/getInstitute", async (ctx, next) => {
    // "select grade.grade_id, grade.grade_name, grade.grade_num, grade.class_num, institute.institute_id, institute.institute_name from grade join institute on grade.institute_id = institute.institute_id limit ? offset ?"
    console.log(ctx.query);
    let query = ctx.query;
    let sql = "select * from institute limit ? offset ?";
    let res = await ctx.db.EXCUTE(sql, [1 * query.pageSize, query.pageSize * (query.pageNum - 1)])

    let gradeSql = 'select grade.grade_name,institute.institute_id,grade.grade_id from grade join institute on grade.institute_id=institute.institute_id'
    let gradeRes = await ctx.db.EXCUTE(gradeSql, [])
    // console.log('年级名字：', gradeRes);
    let totalSql = 'select COUNT(*) as num from institute'
    let totalRes = await ctx.db.EXCUTE(totalSql, [])
    ctx.body = { code: 200, msg: "查询成功", data: { InList: res, total: totalRes[0].num, GrList: gradeRes } }
})
// 搜索学院信息
router.post('/searchInstitute', async (ctx, next) => {
    let query = ctx.query;
    let data = ctx.request.body;
    let sql = `select * from institute where institute_name LIKE '%${data.keyword}%'`;
    let res = await ctx.db.EXCUTE(sql, [])
    let list = res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize)

    let gradeSql = 'select grade.grade_name,institute.institute_id,grade.grade_id from grade join institute on grade.institute_id=institute.institute_id'
    let gradeRes = await ctx.db.EXCUTE(gradeSql, [])
    ctx.body = { code: 200, msg: "查询成功", data: { InList: list, total: res.length, GrList: gradeRes } }
})
// 修改学院信息
router.post('/editInstitute', async (ctx, next) => {
    let query = ctx.query;
    let data = ctx.request.body;
    let hassql = 'select institute_name from institute where institute_name=?'
    let hasRes = await ctx.db.EXCUTE(hassql, [data.institute_name])
    if (hasRes.length > 0) {
        ctx.body = { code: 201, msg: '该学院已存在' }
    } else {
        let sql = 'update institute set institute_name=? where institute_id=?'
        let res = await ctx.db.EXCUTE(sql, [data.institute_name, query.institute_id + ''])
        console.log(res);
        ctx.body = { code: 200, msg: '修改成功' }
    }
})
// 添加学院信息
router.post('/addInstitute', async (ctx, next) => {
    let data = ctx.request.body;
    let hassql = 'select institute_name from institute where institute_name=?'
    let hasRes = await ctx.db.EXCUTE(hassql, [data.institute_name])
    if (hasRes.length > 0) {
        ctx.body = { code: 201, msg: '该学院已存在' }
    } else {
        // 此处的创建学生ID传入的为1
        let id = ctx.snowFlake.createSnow(2);
        let sql = 'insert into institute (institute_name,institute_id) values (?,?)'
        let res = await ctx.db.EXCUTE(sql, [data.institute_name, id])
        console.log(res);
        ctx.body = { code: 200, msg: '添加成功' }
    }
})
// 删除学院信息
router.post('/delInstitute', async (ctx, next) => {
    let data = ctx.request.body;
    let sql = 'delete from institute where institute_id=?'
    let res = await ctx.db.EXCUTE(sql, [data.institute_id])
    console.log(res);
    ctx.body = { code: 200, msg: '删除成功' }
})

module.exports = router